package com.ruoyi.report.mapper;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;

import com.ruoyi.report.domain.*;
import com.ruoyi.report.dto.FileGroupDTO;
import com.ruoyi.report.dto.ReportDataDTO;
import org.apache.ibatis.annotations.Select;

/**
 * 采购计划Mapper接口
 * 
 * @author ruoyi
 * @date 2024-10-12
 */
public interface ReportPlanMapper 
{
    /**
     * 查询采购计划
     * 
     * @param id 采购计划ID
     * @return 采购计划
     */
    public ReportPlan selectReportPlanById(Long id);

    /**
     * 查询采购计划列表
     * 
     * @param reportPlan 采购计划
     * @return 采购计划集合
     */
    public List<ReportPlan> selectReportPlanList(ReportPlan reportPlan);

    /**
     * 新增采购计划
     * 
     * @param reportPlan 采购计划
     * @return 结果
     */
    public int insertReportPlan(ReportPlan reportPlan);



    @Select("SELECT COUNT(*) FROM report_plan " +
            "WHERE MONTH(create_time) = MONTH(CURRENT_DATE()) " +
            "AND YEAR(create_time) = YEAR(CURRENT_DATE())")
    int countCurrentMonthPlans();
    /**
     * 修改采购计划
     * 
     * @param reportPlan 采购计划
     * @return 结果
     */
    public int updateReportPlan(ReportPlan reportPlan);

    /**
     * 删除采购计划
     * 
     * @param id 采购计划ID
     * @return 结果
     */
    public int deleteReportPlanById(Long id);

    /**
     * 批量删除采购计划
     * 
     * @param ids 需要删除的数据ID
     * @return 结果
     */
    public int deleteReportPlanByIds(Long[] ids);

    /**
     * 批量删除${subTable.functionName}
     * 
     * @param customerIds 需要删除的数据ID
     * @return 结果
     */
    public int deleteReportPlanDetailByPlanIds(Long[] ids);
    
    /**
     * 批量新增${subTable.functionName}
     * 
     * @param reportPlanDetailList ${subTable.functionName}列表
     * @return 结果
     */
    public int batchReportPlanDetail(List<ReportPlanDetail> reportPlanDetailList);
    

    /**
     * 通过采购计划ID删除${subTable.functionName}信息
     * 
     * @param id 采购计划ID
     * @return 结果
     */
    public int deleteReportPlanDetailByPlanId(Long id);



@Select("SELECT \n" +
        "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 7 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_1,\n" +
        "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 6 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_2,\n" +
        "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 5 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_3,\n" +
        "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 4 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_4,\n" +
        "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 3 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_5,\n" +
        "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 2 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_6,\n" +
        "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 1 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_7,\n" +
        "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() THEN fuel_quantity ELSE 0 END), 0) AS day_8\n" +
        "FROM \n" +
        "    report_gas_station\n" +
        "WHERE \n" +
        "    name >= CURDATE() - INTERVAL 7 DAY")
public DaysDTO queryLasa7days();



    @Select("SELECT \n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 7 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_1,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 6 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_2,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 5 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_3,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 4 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_4,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 3 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_5,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 2 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_6,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 1 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_7,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() THEN fuel_quantity ELSE 0 END), 0) AS day_8\n" +
            "FROM \n" +
            "    report_sc\n" +
            "WHERE \n" +
            "    name >= CURDATE() - INTERVAL 7 DAY AND dept_id=108")
    public DaysDTO queryAli7days();

    @Select("SELECT \n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 7 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_1,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 6 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_2,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 5 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_3,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 4 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_4,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 3 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_5,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 2 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_6,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 1 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_7,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() THEN fuel_quantity ELSE 0 END), 0) AS day_8\n" +
            "FROM \n" +
            "    report_sc\n" +
            "WHERE \n" +
            "    name >= CURDATE() - INTERVAL 7 DAY AND dept_id=107")
    DaysDTO queryLinzhi7days();
    @Select("SELECT \n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 7 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_1,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 6 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_2,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 5 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_3,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 4 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_4,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 3 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_5,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 2 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_6,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 1 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_7,\n" +
            "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() THEN fuel_quantity ELSE 0 END), 0) AS day_8\n" +
            "FROM \n" +
            "    report_sc\n" +
            "WHERE \n" +
            "    name >= CURDATE() - INTERVAL 7 DAY AND dept_id=109")
    DaysDTO queryRikaze7days();



 @Select("SELECT \n" +
         "    e.exam_code as examCode,exam_name as examName,\n" +
         "    COUNT(CASE WHEN (u.exam_score / u.questions_score) * 100 < 60 THEN 1 END) AS \"percentOne\",\n" +
         "    COUNT(CASE WHEN (u.exam_score / u.questions_score) * 100 >= 60 AND (u.exam_score / u.questions_score) * 100 < 80 THEN 1 END) AS \"percentTwo\",\n" +
         "    COUNT(CASE WHEN (u.exam_score / u.questions_score) * 100 >= 80 THEN 1 END) AS \"percentThree\"\n" +
         "FROM (\n" +
         "    SELECT exam_code,exam_name\n" +
         "    FROM exam_task_manager\n" +
         "    WHERE   -- 考试类型\n" +
         "       status = 0  -- 状态为 0\n" +
         "      AND start_time <= NOW()  -- 确保考试已开始\n" +
         "    ORDER BY start_time DESC\n" +
         "    LIMIT 7\n" +
         ") e\n" +
         "LEFT JOIN exam_user_score u ON e.exam_code = u.exam_code\n" +
         "\n" +
         "GROUP BY e.exam_code;\n")
    List<ExamScoreStatistics> getExamScoreStatistics();
@Select("SELECT\n" +
        "    rpd.refinery_id,\n" +
        "    r.refinery_name AS refineryName,\n" +
        "    COALESCE(SUM(rpd.quantity), 0) AS totalAllocation,\n" +
        "    COALESCE(SUM(ro.send_weight), 0) AS totalSendWeight,\n" +
        "    COALESCE(SUM(ro.send_car_count), 0) AS totalSendCarCount\n" +
        "FROM\n" +
        "    report_plan rp\n" +
        "LEFT JOIN\n" +
        "    report_plan_detail rpd ON rp.id = rpd.plan_id\n" +
        "LEFT JOIN\n" +
        "    report_onway ro ON rp.id = ro.plan_id AND rpd.refinery_id = ro.refinery_id  \n" +
        "LEFT JOIN\n" +
        "    report_refinery_info r ON rpd.refinery_id = r.id  \n" +
        "WHERE\n" +
        "    rp.id = (\n" +
        "        SELECT id\n" +
        "        FROM report_plan\n" +
        "        ORDER BY create_time DESC\n" +
        "        LIMIT 1\n" +
        "    )\n" +
        "GROUP BY\n" +
        "    rpd.refinery_id, r.refinery_name;\n")
    List<RefineryAllocation> getRefineryAllocations();
@Select("SELECT \n" +
        "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 7 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_1,\n" +
        "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 6 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_2,\n" +
        "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 5 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_3,\n" +
        "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 4 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_4,\n" +
        "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 3 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_5,\n" +
        "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 2 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_6,\n" +
        "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() - INTERVAL 1 DAY THEN fuel_quantity ELSE 0 END), 0) AS day_7,\n" +
        "    COALESCE(SUM(CASE WHEN DATE(name) = CURDATE() THEN fuel_quantity ELSE 0 END), 0) AS day_8\n" +
        "FROM \n" +
        "    report_gas_station\n" +
        "WHERE \n" +
        "    name >= CURDATE() - INTERVAL 7 DAY")
    DaysDTO queryPeisong7days();

     List<ReportPlanInfo> selectReportPlanInfoList(ReportPlan reportPlan);

    ReportDataDTO queryLasaDaily(String date);

ReportDataDTO queryLinzhiDaily(String date);

ReportDataDTO queryRikazeDaily(String date);

ReportDataDTO queryAliDaily(String date);

ReportDataDTO queryPeisongzhongxinDaily(String date);
@Select("SELECT \n" +
        "    file_suffix AS fileType, \n" +
        "    COUNT(*) AS fileCount\n" +
        "FROM \n" +
        "    disk_file\n" +
        "WHERE \n" +
        "    is_dir = 0 \n" +
        "    AND del_flag = '0' \n" +
        "GROUP BY \n" +
        "    file_suffix\n" +
        "ORDER BY \n" +
        "    fileCount DESC;\n")
    List<FileGroupDTO> selectFileGroup();
}
